** As several datasets used in the analysis are proprietary and cannot be shared, 
** this code is only given to help reserachers to understand the set up of our 
** databases and main variables.


*******************************************************************************
* This file produces a tidy version of the datasets that are later used for 
* regressions in the paper Activity Shocks and Corporate Liquidity:
* the Role of Trade Credit.
*
* Script: do/Prepare_data_regressions.do
* Authors:	
*			Anne Duquerroy  [anne.duquerroy@banque-france.fr]
*			Benjamin Bureau [benjamin.bureau@banque-france.fr]
*			Frédéric Vinas  [frederic.vinas@banque-france.fr]
* Date: 	Jan. 19, 2024
********************************************************************************
		
		* input:
		*	../$mypath2/fiben_2018_SCR_IP.dta
		*   ../$datapath/altman_z_score_naf_cotation
		*   ../$datapath/douteux
		
		* output:
		* ../$mypath2/reg_cie_unbalanced_exhaustive
		* ../$mypath2/reg_cie
		* ../$mypath2/regression_sample_final
		
		**********************************************			
		use $mypath2/fiben_2018_SCR_IP, replace
		**********************************************
				
		** Post lockdown dummy
		
		gen post=(date>=ym(2020,3))
		
		forvalues i=3(1)12 {
			gen postm`i'20=(date==ym(2020,`i'))		
		}
		
		
		xtset siren date
		
		** -- Define FE 
		
		* year#firm
		egen siren_year = group(siren fyear) 
		
		* ind#time
		egen ind_year  = group(naf4d fyear)
		egen ind_month = group(naf4d date)
	
		** -- CIE
		** -- Take the opposite i.e. Payables net of Receivables, then standardize
		replace cie_ca = - cie_ca 
		egen 	cie_sd   = sd(cie_ca)
		gen 	cie_std   = cie_ca / cie_sd
		drop 	cie_ca 
		rename 	cie_std  cie_ca
		lab var cie_ca "TC ratio over sales, standardized"
		
		gen 	cie_post  = cie_ca * post
		lab var cie_post  "TC ratio over sales, standardized X Post"
		
		forvalues i=3(1)12 {
			gen cie_post`i'=cie_ca * postm`i'20
		}

		** -- Standardize Ratio of Payables and Receivables
		foreach var in ccca dfac {
		egen `var'_sd   = sd(`var')
		gen `var'_std   = `var'/ `var'_sd
		drop `var'_sd
		
		drop 	ccca dfac
		rename 	ccca_std  ccca
		rename 	dfac_std  dfac
		
		gen `var'_post 	= `var' * post
			forvalues i=3(1)12 {
				gen `var'_post`i'=`var' * postm`i'20
			}
		lab var ccca "Receivables over Sales, standardized"
		lab var dfac "Payables over Purchases, standardized"
		
		** Size = log of total assets
		gen size = ln(ta_lag)
	
		
		** Standardize Covariates
		foreach var in size  cash_ta  leverage {
		egen `var'_sd   = sd(`var')
		gen `var'_std   = `var'/ `var'_sd
		drop `var'_sd
		drop `var'
		rename `var'_std `var'
		}
		
		** Covariates x post		
		gen size_post 		= size 		* post
		gen cash_post 		= cash_ta 	* post
		gen dette_post 		= leverage 	* post


		** -- Firm characteristics
		** value added to capital ratio
		gen mrpk1 		= vaj   / capi_expl
		** Return on Assets
		gen roa 		= ene/ta_lag
		gen fixed_asset = immophy / ta_lag
		gen payout  	= div_pay / ene
		gen payer 		= (div_pay>0)
		
		
		** Winsorize
		foreach var in mrpk1 roa fixed_asset payout  {
		winsor `var'  , gen(`var'w) p(0.01)
		drop `var'
		rename `var'w `var'
		}
		
		** Standardize
		foreach var in mrpk1 roa fixed_asset payout  {
		egen `var'_sd   = sd(`var')
		gen `var'_std   = `var'/ `var'_sd
		drop `var'_sd
		drop `var'
		rename `var'_std `var'
		}
						
		
		** -- Time dummies interacted with trade credit ratio
		cap drop m_date cie_dum*
		
		egen m_date = group(date) if fyear >=2018 
		local m1 = 1
		local m2 = 24
		forvalues m=`m1'/`m2' {
			gen cie_dum_`m' = cie_ca*(m_date==`m')
		}
		
		** -- March & April vs May  June 
		gen march_april =(date>=ym(2020,3) & (date<=ym(2020,4)))
		gen may_june    =(date>=ym(2020,5) & (date<=ym(2020,6)))
		foreach var in march_april may_june {
		gen cie_`var' = cie * `var'
		}
		
		** -- Rating as of January 2020 
		gen 	rating_j = rating_num if date == tm(2020m1)
		bys siren : egen rating_jan =max(rating_j)
		drop rating_j
		replace rating_jan = 0.5 if rating_jan == .
		gen not_rated = (rating_jan == 0.5)
		
		replace firm_with_ip=0 if firm_with_ip==.	
		
		******************************
		save $mypath2/reg_cie, replace
		******************************
		
		drop year
		gen year = yofd(dofm(date))
		drop if year == 2018
		drop if year == 2017

		********************************************************
		save $mypath2/reg_cie_unbalanced_exhaustive.dta, replace
		********************************************************
		
		// Balanced Panel 
		*******************************************************
		use $mypath2/reg_cie_unbalanced_exhaustive.dta, replace
		*******************************************************
		
		** Drop firms having already filed with bankruptcy before Covid
		drop if rating_jan == 12
		
		** -- Balanced panel 
		gen i = 1
		bys siren : egen pres =sum(i) if  date < tm(2020m7)
		sum pres,d
		keep if pres == 18
		
		** Regression Spec
		global  control		 		cash_ta 	  leverage		 size
		global  control_post		cash_ta_post  leverage_post   size_post
		global regspec_c  			"absorb (siren ind_month) vce (cluster i.siren)"
	
		foreach var in $control $control_post {
		replace `var' = 0 if `var' ==.
		}
		
		** -- Define regression sample
		reghdfe firm_with_ip cie_std cie_std_post    , $regspec_c
		gen in_sample =e(sample)
		keep if in_sample ==1	
		
		*********************************************
		save $mypath2/regression_sample.dta, replace
		*********************************************
	

		*----------------------------------------------------------------------------------------------------------*
		*----------------------------------------------------------------------------------------------------------*
		
		** -- Add information on Z score and Doubtful loans 
		
		** Z score 
		
		****************************************************
		use $datapath\altman_z_score_naf_cotation , replace
		****************************************************
		
		sort siren year
		replace year = year + 1
		merge 1:m siren year using $mypath2/regression_sample.dta
		drop if _m == 1
		drop _m
		
		tsset siren date

		winsor altman_score, gen(altman_w) p(0.01)
		sum altman_w,d
		gen altman_post = altman_w*post
		

		** NPL
		
		merge 1:1 siren date using $mypath2\douteux
		drop if _m == 2
		
		gen douteux_post  = ratio_douteux * post
		gen douteux_march = ratio_douteux * march_april
		gen douteux_may   = ratio_douteux * may_june
		
		gen st_post  = share_st_lag3 * post
		gen st_march = share_st_lag3 * march_april
		gen st_may   = share_st_lag3 * may_june
	

		** Most constrained specification 
		reghdfe firm_with_ip share_st_lag3 ratio_douteux altman_w altman_post douteux_post st_post cie_march cie_may cie  $control $control_post if date < tm(2020m7) , $regspec_c
		gen in_s = e(sample)
		keep if in_s ==1
		keep siren
		duplicates drop
		*************************************
		save $mypath2/siren_list.dta, replace
		**************************************
		
		
		****************************************************
		use $mypath2/temp.dta, replace
		****************************************************
		drop _m
		merge m:1 siren  using $mypath2/siren_list
		keep if _m ==3
		drop _m
		****************************************************
		save $mypath2/regression_sample_final.dta, replace
		****************************************************
